package com.yulian.repository.supplier_management;

import java.util.Iterator;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonObject;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

public class PartnerCommissionRatioRepositoryImpl {

    final InternalLogger logger = Log4JLoggerFactory.getInstance(PartnerCommissionRatioRepositoryImpl.class);

    /**
     * 获取记录总数
     *
     * @param conn
     * @param queryJSObj
     * @return
     */
    public Future<RowSet<Row>> getRecordsTotal(SqlConnection conn, JsonObject queryJSObj) {
        Promise<RowSet<Row>> promise = Promise.promise();
        //首先取出符合条件的总的记录数
        String sql = this.creaStrGetRecordsTotalWithJson(queryJSObj);
        conn
        .preparedQuery(sql)
        .execute(ar2 -> {
            //Release the connection to the pool
            //conn.close();
            if (ar2.succeeded()) {
                promise.complete(ar2.result());
            } else {
                logger.info("getRecordsTotal, query Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件的记录数的 sql语句
     *
     * @param queryJS
     * @return
     */
    public String creaStrGetRecordsTotalWithJson(JsonObject queryJS) {

        // define SelectQuery object
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql(" count(*) as total "))
        .addCustomFromTable("partner_commission_ratio");

        //common part to add InCondition
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
//						  logger.info(key+ ": "+ value.toString());
                if (!value.equals("") && !value.equals("[]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    //添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }

            } catch (Exception e) {
                // Something went wrong!
            }
        }
        return sq.validate().toString();

    }

    /**
     * 获取记录
     *
     * @param conn
     * @param queryJSObj
     * @param query_offset
     * @param query_limit
     * @return
     */
    public Future<RowSet<Row>> getRecords(SqlConnection conn, JsonObject queryJSObj, int query_offset, int query_limit) {
        Promise<RowSet<Row>> promise = Promise.promise();
        /***
         * get_records with queryJSObj
         */
        //取出满足条件的记录
        String sql = this.creaStrGetRecordsWithJson(queryJSObj, query_offset, query_limit);
        conn
        .preparedQuery(sql)
        .execute(ar2 -> {
            //Release the connection to the pool
            conn.close();
            if (ar2.succeeded()) {
//	        	logger.info("getRecords, query success: "+ ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                logger.info("getRecords, query Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录 的sql语句
     *
     * @param queryJS
     * @param query_offset
     * @param query_limit
     * @return
     */
    public String creaStrGetRecordsWithJson(JsonObject queryJS, int query_offset, int query_limit) {

        // define SelectQuery object
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_code"), new CustomSql("class_lv1_code"),
        new CustomSql("gross_margin_range"), new CustomSql("supply_partner_commission_ratio"), new CustomSql("processing_manager_commission_ratio"),
        new CustomSql("sale_partner_commission_ratio"), new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time")
        )
        .addCustomFromTable("partner_commission_ratio");

        //common part to add InCondition
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    //添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                // Something went wrong!
            }
        }
        return sq.validate().toString() + " order by id desc  limit " + query_limit + " offset " + query_offset;
    }

    /***
     *
     * @param search_type
     * @param queryJS
     * @return
     */
    public String createSqlStr(String search_type, JsonObject queryJS) {
        // define SelectQuery object
        SelectQuery sq = new SelectQuery();
        if (search_type.equals("get_site")) {
            //获取partner_commission_ratio 表中含有的销售地区
            sq.addCustomColumns(new CustomSql("distinct site_code"))
            .addCustomFromTable("partner_commission_ratio");
        } else if (search_type.equals("get_class_lv1")) {
            //获取partner_commission_ratio 表中含有的商品一级分类编码
            sq.addCustomColumns(new CustomSql("distinct class_lv1_code"))
            .addCustomFromTable("partner_commission_ratio");
        } else if (search_type.equals("get_is_admit")) {
            // 获取生效状态
            sq.addCustomColumns(new CustomSql("distinct is_admit"))
            .addCustomFromTable("payment_access");
        }

        //common part to add InCondition
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                //传入value 是形如[c1, c2] 或者[] 的字符串数组
                if (!value.equals("") && !value.equals("[]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 最后字符串形式为： c1','c2','c3 ;
                    //添加where  InCondtion 子句；生成如下语句：  class_lv1_code in ('c1','c2','c3')
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }

            } catch (Exception e) {
                // Something went wrong!
            }
        }
        return sq.validate().toString();
    }

    /**
     * 此方法判断传入的销售地区和一级商品名分类是否存在
     *
     * @param conn
     * @param site_code
     * @return
     */
    public Future<RowSet<Row>> getPartner_Commission_RatioBysite_lv1_code(SqlConnection conn, String site_code, String class_lv1_code) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "select * from partner_commission_ratio where site_code = $1 and class_lv1_code = $2 order by id desc limit 1";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(site_code, class_lv1_code), ar -> {
            conn.close();
            if (ar.succeeded()) {
                logger.info("getPartner_Commission_RatioBysite_lv1_code, 记录数: ", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("getPartner_Commission_RatioBysite_lv1_code, 失败,失败原因:{}", ar.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }


    /***
     *
     * @param conn 连接
     * @param site_code 销售地区
     * @param class_lv1_code 商品一级编码
     * @param gross_margin_range 到岸价毛利率区间
     * @param supply_partner_commission_ratio 供应链合伙人提成比例额
     * @param processing_manager_commission_ratio 加工中心管理人提成比列
     * @param sale_partner_commission_ratio 销售合伙人提成比例
     * @param create_user 创建人
     * @return
     */
    public Future<RowSet<Row>> insertPartnerCommissionRatio(SqlConnection conn, String site_code, String class_lv1_code, String gross_margin_range, float supply_partner_commission_ratio, float processing_manager_commission_ratio, float sale_partner_commission_ratio, String create_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "INSERT INTO partner_commission_ratio(site_code, class_lv1_code, gross_margin_range, supply_partner_commission_ratio, processing_manager_commission_ratio, sale_partner_commission_ratio,create_user)"
        + " VALUES ($1, $2, $3, $4, $5, $6, $7)  RETURNING id";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(site_code, class_lv1_code, gross_margin_range, supply_partner_commission_ratio, processing_manager_commission_ratio, sale_partner_commission_ratio, create_user), ar2 -> {
            //释放一个连接到连接池
            conn.close();
            if (ar2.succeeded()) {
                logger.info("insertPartnerCommissionRatio, 插入记录数:{} ", ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                logger.info("insertPartnerCommissionRatio, 插入失败,失败原因:{}", ar2.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /***
     * 根据ID跟新GoodPool中的数据
     * @param conn
     * @param id
     * @param gross_margin_range
     * @param supply_partner_commission_ratio
     * @param processing_manager_commission_ratio
     * @param sale_partner_commission_ratio
     * @param update_user
     * @return
     */
    public Future<RowSet<Row>> updatePartnerCommissionRatioById(SqlConnection conn, long id, String gross_margin_range, float supply_partner_commission_ratio,
                                                                float processing_manager_commission_ratio, float sale_partner_commission_ratio, String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "UPDATE partner_commission_ratio SET gross_margin_range = $1, supply_partner_commission_ratio = $2, processing_manager_commission_ratio = $3, sale_partner_commission_ratio = $4, update_user = $5 WHERE id = $6 RETURNING id";
        conn.preparedQuery(sql).execute(Tuple.of( gross_margin_range, supply_partner_commission_ratio, processing_manager_commission_ratio, sale_partner_commission_ratio,
        update_user, id), ar -> {
            // Release the connection to the pool
            conn.close();
            if (ar.succeeded()) {
                logger.info("updatePartnerCommissionRatioById, 更新记录数: {}", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("updatePartnerCommissionRatioById, 更新失败, 失败原因: {}", ar.cause().getMessage());
                // 这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    /***
     * 插入数据时，如果区域数据和一级商品名分类重复，则覆盖
     * @param conn
     * @param site_code
     * @param class_lv1_code
     * @param gross_margin_range
     * @param supply_partner_commission_ratio
     * @param processing_manager_commission_ratio
     * @param sale_partner_commission_ratio
     * @param update_user
     * @return
     */
    public Future<RowSet<Row>> updatePartnerCommissionRatioBysite_lv1_code(SqlConnection conn, String site_code, String class_lv1_code, String gross_margin_range, float supply_partner_commission_ratio,
                                                                           float processing_manager_commission_ratio, float sale_partner_commission_ratio, String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "UPDATE partner_commission_ratio SET site_code = $1, class_lv1_code = $2, gross_margin_range = $3, supply_partner_commission_ratio = $4, processing_manager_commission_ratio = $5, sale_partner_commission_ratio = $6,update_user = $7 WHERE site_code = $8 and class_lv1_code = $9 RETURNING id";
        conn.preparedQuery(sql).execute(Tuple.of(site_code, class_lv1_code, gross_margin_range, supply_partner_commission_ratio, processing_manager_commission_ratio, sale_partner_commission_ratio,
        update_user, site_code, class_lv1_code), ar -> {
            // Release the connection to the pool
            conn.close();
            if (ar.succeeded()) {
                logger.info("updatePartnerCommissionRatioBysite_lv1_code, 更新记录数: {}", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("updatePartnerCommissionRatioBysite_lv1_code, 更新失败, 失败原因: {}", ar.cause().getMessage());
                // 这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    public Future<RowSet<Row>> deletePartnerCommissionRatio(SqlConnection conn, int is_delete, String id_list, String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "update partner_commission_ratio set is_delete = $1, update_user = $2 where id = any(array" + id_list + ") RETURNING id";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(is_delete, update_user), ar -> {
            conn.close();
            if (ar.succeeded()) {
                logger.info("deletePartnerCommissionRatio, 逻辑删除记录数:{} ", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("deletePartnerCommissionRatio, 逻辑删除失败,失败原因:{}", ar.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    public Future<RowSet<Row>> deletePartnerCommissionRatioByID(SqlConnection conn, long id, int is_delete, String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "update partner_commission_ratio set is_delete = $1, update_user = $2 where id = $3 RETURNING id";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(is_delete, update_user, id), ar -> {
            conn.close();
            if (ar.succeeded()) {
                logger.info("deletePartnerCommissionRatio, 逻辑删除记录数:{} ", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("deletePartnerCommissionRatio, 逻辑删除失败,失败原因:{}", ar.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

}
